import os
import requests
import sys
import io
import json
import pymysql
import time
import xlsxwriter
from openpyxl import Workbook, load_workbook
from PIL import Image as Image2
from openpyxl.drawing.image import Image


def save_image(input_name, output_name):
    im = Image2.open(input_name)
    if im.mode == "RGBA":
        im.load()  # required for png.split()
        background = Image2.new("RGB", im.size, (255, 255, 255))
        background.paste(im, mask=im.split()[3])  # 3 is the alpha channel
        im = background
    img_y = Image(input_name)
    if img_y.width > 1000 or img_y.height > 1000:
        size_h = size_w = 800
        if img_y.width > img_y.height:
            new_size = (size_w, int(img_y.height * (size_w / img_y.width)))
        else:
            new_size = (int(img_y.width * (size_h / img_y.height)), size_h)
        im = im.resize(new_size, Image2.ANTIALIAS)  # 转化图片
        im.save('{}.jpg'.format(output_name), 'JPEG', quality=40)
    else:
        im.save('{}.jpg'.format(output_name), 'JPEG')


def ceshi2(id, yuan):
    # db = pymysql.connect("192.168.0.41", "pyexcel", "smw123", "imgup")
    db = pymysql.connect("127.0.0.1", "root", "root", "imgup")

    cursor = db.cursor()
    sql = "SELECT b.url, a.created_at, a.attach_sn, a.attach_color, b.label_names FROM img_main a " + \
          "INNER JOIN img_url b ON a.id = b.main_id " + \
          "INNER JOIN img_z_excel c ON a.id = c.main_id " + \
          "where c.outexcel_id = " + str(id)
    # 执行SQL语句
    cursor.execute(sql)
    # 获取所有记录列表x
    data = cursor.fetchall()
    # print(data[0][1].replace('big', 'mini'), yuan == 1);exit()
    # print(data);exit;

    t = time.time()
    name = (str(round(t * 1000000))) + '.xlsx'
    fn = r"E:\phpStudy\PHPTutorial\WWW\tp6\laravel\imgUp\storage\app\pyExcel\%s" % name
    wb = xlsxwriter.Workbook(fn)
    sheet = wb.add_worksheet()

    # 设置文字图片单元格的行高列宽
    row_height = 90
    size_w = 120
    size_h = 120
    c = 2
    i = 1
    sheet.set_column(0, 0, 5)
    sheet.set_column(1, 1, 20)
    sheet.set_column(2, 2, 15)
    sheet.set_column(3, 3, 15)
    sheet.set_column(4, 4, 10)
    sheet.set_column(5, 5, 80)
    sheet.write_row(0, 0, ('序号', '图片', '上传日期', '型体编号', '颜色', '标签属性'))
    format2 = wb.add_format({'num_format': 'yy/mm/dd hh:mm'})
    for col2 in data:
        sheet.set_row(c-1, 120)
        sheet.write_row(c-1, 0, (c-1, '') + col2[1:5])
        sheet.write('C' + str(c), col2[1], format2)  # 28/02/13
        # img = Image(r'E:\s41www\tp6\laravel\imgup\public\%s' % col2[2])
        f_src = r'E:\phpStudy\PHPTutorial\WWW\tp6\laravel\imgUp\public\%s' % col2[0]
        f = r"E:\phpStudy\PHPTutorial\WWW\tp6\laravel\imgUp\storage\app\pyExcel\%s" % str(i)
        save_image(f_src, f)
        img = Image(f + '.jpg')
        f_src = f + '.jpg'
        i += 1
        # new_size = (90, 90)
        # img.width, img.height = new_size  # 这两个属性分别是对应添加图片的宽高width
        # height
        if img.width > img.height:
            new_size = round(size_w / img.width, 2)
        else:
            new_size = round(size_h / img.height, 2)
        # print(new_size, img.width, img.height)
        sheet.insert_image('B' + str(c), f_src, {'x_offset': 2, 'y_offset': 2, 'x_scale': new_size, 'y_scale': new_size})
        c += 1
    wb.close()
    return name


if __name__ == "__main__":
    # data = sys.argv[1]
    # json = json.loads(data)
    # res = ceshi()
    # print(sys.argv);exit()
    # res = ceshi2(1, 0)
    res = ceshi2(int(sys.argv[1]), int(sys.argv[2]))
    print(res)
    exit()
    # id = test(int(sys.argv[1]))
    # print(int(sys.argv[1]), ok)
    # print(sys.argv)

    url = "http://127.0.0.1/tp6/laravel/imgUp/public/index.php/api/pyFile"
    clientId = "1111"
    clientKey = "2222"
    timestamp = (str)(int(round(time.time() * 1000)))
    header = {'clientId': clientId, 'timestap': timestamp}
    files = {'file': open('./' + res, 'rb')}
    data = {'enctype': 'multipart/form-data', 'name': 'wang'}
    response = requests.post(url, data=data, headers=header, files=files)
    text = response.text
    print(text)



